//
//  DBManagerHelper.m
//  test
//
//  Created by mac on 2017/9/14.
//  Copyright © 2017年 mac. All rights reserved.
//

#import "DBManagerHelper.h"



@implementation DBManagerHelper
{
    //数据库对象
    FMDatabase *_myDatabase;
}

//获取单例对象
+(DBManagerHelper *)instance
{
    static DBManagerHelper *manager=nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken,^{
        manager=[[DBManagerHelper alloc]init];
    });
    return manager;
}

-(instancetype)init
{
    if(self=[super init])
    {
        //初始化数据库对象
        [self createDataBase];
    }
    return self;
}

-(void)createDataBase
{
    
    //数据库文件的路径
    NSString *path=[NSHomeDirectory() stringByAppendingPathComponent:@"Documents/product.sqlite"];
    NSLog(@"pat=%@",path);
    //1.初始化数据库对象
    _myDatabase=[[FMDatabase alloc]initWithPath:path];
    
    //2.打开数据库
    BOOL ret=[_myDatabase open];
    if(!ret){
        NSLog(@"数据库打开失败:%@",_myDatabase.lastErrorMessage);
    }else{
        //打开成功
        
        //3.创建表格
        //primary key 表示userId是主键,唯一的表示一条记录.
        //autoincrement 表示数据库自己维护主键值的变化.
        //blob是类型,相当于NSData
        //NSString *createsql=@"create table if not exists product (Id integer primary key autoincrement,report_date varchar(255),post_code varchar(255),store_name varchar(255),brand varchar(255),reason_name varchar(255))";
        NSString *createsql=@"create table if not exists product (Id integer primary key autoincrement,report_date varchar(255),post_code varchar(255),store_name varchar(255),brand varchar(255),reason_name varchar(255))";
        
        BOOL flag=[_myDatabase executeUpdate:createsql];
        [_myDatabase executeUpdate:[NSString stringWithFormat:@"create index product on (brand)"]];//建立索引
        if(!flag){
            NSLog(@"创建表格失败:%@",_myDatabase.lastErrorMessage);
        }else{
            NSLog(@"创建表格成功");
        }
    }
}

-(void)insertProduct:(Product *)model
{
    NSString *insertsql=@"insert into product (report_date,post_code,store_name,brand,reason_name) values (?,?,?,?,?)";
    //model.image转化为二进制
    BOOL ret=[_myDatabase executeUpdate:insertsql
              ,model.report_date
              ,model.post_code
              ,model.store_name
              ,model.brand
              ,model.reason_name];
    if(!ret)
    {
        NSLog(@"添加用户失败:%@",_myDatabase.lastErrorMessage);
    }
}

-(NSArray *)selectAllProduct
{
    NSString *selectsql=@"select * from product";
    //用游标的方式来管理数据
    FMResultSet *set=[_myDatabase executeQuery:selectsql];
    NSMutableArray *array=[NSMutableArray array];
    while ([set next]) {
        //创建对象
        Product *model=[[Product alloc]init];
        model.Id = [set intForColumn:@"Id"];
        model.report_date = [set stringForColumn:@"report_date"];
        model.post_code = [set stringForColumn:@"post_code"];
        model.store_name = [set stringForColumn:@"store_name"];
        model.brand = [set stringForColumn:@"brand"];
        model.reason_name = [set stringForColumn:@"reason_name"];
        
        [array addObject:model];
    }
    return array;
}

-(void)deleteProductWithId:(int)userId
{
    NSString *deletesql=@"delete from product where Id=?";
    BOOL ret=[_myDatabase executeUpdate:deletesql,@(userId)];
    if(!ret)
    {
        NSLog(@"删除用户失败:%@",_myDatabase.lastErrorMessage);
    }
}

-(void)updateProduct:(Product *)model Id:(int)Id
{
    
    NSString *updatesql=@"update product set report_date=?,post_code=?,store_name=?,brand=?,reason_name=? where Id=?";
    BOOL ret=[_myDatabase executeUpdate:updatesql
              ,model.report_date
              ,model.post_code
              ,model.store_name
              ,model.brand
              ,model.reason_name,@(Id)];
    if(!ret)
    {
        NSLog(@"修改失败:%@",_myDatabase.lastErrorMessage);
    }
}


-(void) delectFrom{
    NSDate *startTime = [NSDate date];
    BOOL ret = [_myDatabase executeUpdate:@"DELETE FROM product"];
    if(!ret)
    {
        NSLog(@"删除失败:%@",_myDatabase.lastErrorMessage);
    }
    NSDate *endTime = [NSDate date];
    NSTimeInterval a = [endTime timeIntervalSince1970] - [startTime timeIntervalSince1970];
    NSLog(@"删除数据用时%f秒",a);
}

-(FMResultSet *)selectCategory {
   
    
    NSDate *startTime = [NSDate date];
    NSString *selectsql=@"select brand,count(brand) total from product where report_date='2010-10-10' group by brand";
    //select code,sum(num) total from 表名 group by code order by code
    FMResultSet *set=[_myDatabase executeQuery:selectsql];
    
    while ([set next]) {
        NSLog(@"===%@====%@",[set stringForColumn:@"brand"],[set stringForColumn:@"total"]);
    }
    
    NSDate *endTime = [NSDate date];
    NSTimeInterval a = [endTime timeIntervalSince1970] - [startTime timeIntervalSince1970];
    NSLog(@"查询分类数据用时%f秒",a);
    
    return set;
}


-(int) selectCount{
    NSDate *startTime = [NSDate date];
    FMResultSet *s = [_myDatabase executeQuery:@"SELECT COUNT(*) FROM product"];
    if ([s next]) {
        int count = [s intForColumnIndex:0];
        
        NSDate *endTime = [NSDate date];
        NSTimeInterval a = [endTime timeIntervalSince1970] - [startTime timeIntervalSince1970];
        NSLog(@"查询总量数据用时%f秒",a);
        
        return count;
    }
 
    return 0;
}



/**
 * 使用事务批量插入
 */
- (void)batchInsert:(NSArray *)datas
{
    if ([_myDatabase open]){
        NSDate *startTime = [NSDate date];
        [_myDatabase beginTransaction];
        BOOL isRollBack = NO;
        @try{
            /*for (NSDictionary *item in datas) {
                NSString *updatesql=@"insert into product (report_date,post_code,store_name,brand,reason_name) values (?,?,?,?,?)";
                [_myDatabase executeUpdate:updatesql
                          ,[item objectForKey:@"report_date"]
                          ,[item objectForKey:@"post_code"]
                          ,[item objectForKey:@"store_name"]
                          ,[item objectForKey:@"brand"]
                          ,[item objectForKey:@"reason_name"]];
            }*/
            //测试添加
            for (int i=0;i<200000;i++) {
                NSString *updatesql=@"insert into product (report_date,post_code,store_name,brand,reason_name) values (?,?,?,?,?)";
                int value = arc4random() % 20;
                NSString *stringInt = [NSString stringWithFormat:@"嘎嘎啊就是感觉%i==========得到根深蒂固%i",value,value];
                [_myDatabase executeUpdate:updatesql
                 ,@"2010-10-10"
                 ,@"啊沙发沙发沙发啊沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上沙发啊沙发沙发沙发啊沙发上"
                 ,@"啊沙发沙发沙发啊沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发上"
                 ,stringInt
                 ,@"啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发沙发沙发啊沙发沙发沙发啊沙发上啊沙发沙发沙发啊沙发上"];
            }
            
            NSDate *endTime = [NSDate date];
            NSTimeInterval a = [endTime timeIntervalSince1970] - [startTime timeIntervalSince1970];
            NSLog(@"使用事务插入数据用时%f秒",a);
            
        }@catch (NSException *exception){
            isRollBack = YES;
            [_myDatabase rollback];
        }@finally{
            if (!isRollBack){
               [_myDatabase commit];
            }
        }
        //[_myDatabase close];
    }  
}




@end

